package com.shop.dao;

import java.sql.SQLException;

import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.shop.domain.OrderItem;
import com.shop.domain.Product;
import com.shop.utils.JDBCUtils;

public class RankDao {

	public List<OrderItem> showRank() {
		String sql = "select item.product_id as product_id, sum(item.buynum) as buynum from orders as ord, orderitem as item where ord.id = item.order_id and ord.state =1 GROUP BY item.product_id";
		
		try {
			QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
			List<OrderItem> list = queryRunner.query(sql, new BeanListHandler<OrderItem>(OrderItem.class));
			for(OrderItem item : list){
				String sql2 = "select * from products where id = ?";
				Product product = queryRunner.query(sql2, new BeanHandler<Product>(Product.class),item.getProduct_id());
				item.setProduct(product);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("没有找到相关记录");
		}
	}

}
